﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;
using Ortodoctor.Componentes;

namespace Ortodoctor.modelo
{
    class modeloProcedimento
    {
        //string connectionstring = ("Server=186.202.152.24;Port=5432;User Id=ortodoctor1;Password=DycbqDfHLI;Database=ortodoctor1;MinPoolSize=5;CommandTimeout=60;");
        string connectionstring = ("Server=localhost;Port=5432;User Id=postgres;Password=postgres;Database=ortodoctor;MinPoolSize=5;CommandTimeout=60;"); NpgsqlCommand com;
        NpgsqlConnection conn;
        NpgsqlDataAdapter da;
        //conexao con = new conexao();
        public modeloProcedimento()
        {
            conn = new NpgsqlConnection(connectionstring);
            conn.Open();
        }

        public int insert(Procedimento p)
        {
            object obj;
            try
            {
                //con.open();
                String sql = "INSERT INTO procedimento(tipo, nome_procedimento,promocao) VALUES (:p1, :p2, :p3)";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", p.Tipo);
                com.Parameters.AddWithValue("p2", p.NomeProcedimento);
                com.Parameters.AddWithValue("p3", p.Promocao);
                obj = com.ExecuteScalar();
            }
            finally
            {
                conn.Close();
            }
            return obj != null ? (int.Parse(obj.ToString())) : getMaxId();
        }

        public int getMaxId()
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT max(id_procedimento) as id_procedimento from procedimento";
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return Convert.ToInt32(dt.Rows[0]["id_procedimento"]);
        }

        public DataTable getAll()
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT * FROM procedimento";
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public DataTable getProcedimento(int id)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT * FROM procedimento WHERE id_procedimento=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public void update(Procedimento p)
        {
            try
            {
                //con.open();
                String sql = "UPDATE procedimento SET tipo=:t, nome_procedimento=:n, promocao=:promo WHERE" +
                             " id_procedimento=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("t", p.Tipo);
                com.Parameters.AddWithValue("n", p.NomeProcedimento);
                com.Parameters.AddWithValue("id", p.IdProcedimento);
                com.Parameters.AddWithValue("promo", p.Promocao);
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public void delete(int id)
        {
            try
            {
                //con.open();
                String sql = "DELETE FROM procedimento WHERE id_procedimento=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public DataTable getProcedimentosCliente(int idCliente)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT procedimento_cliente.id_procedimento_cliente, "+
                                    "procedimento.nome_procedimento "+
                               "FROM procedimento " + 
                              "INNER JOIN procedimento_cliente USING(id_procedimento)" + 
                              "WHERE id_cliente = :idCliente";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("idCliente", idCliente);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public string getProcedimentoNome(int id)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "Select nome_procedimento from procedimento where id_procedimento=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally { conn.Close(); }
            return (String)dt.Rows[0][0];
        }

        public string getProcedimentoNome(string id)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "Select nome_procedimento from procedimento where id_procedimento=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally { conn.Close(); }
            return (String)dt.Rows[0][0];
        }
    }
}
